Option Infer On
Imports System.Data.SqlClient
Imports System.Configuration
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.SqlTypes
Imports CrystalDataHub.BLL.DataHub.BusinessObject
Imports Crystal.CLL
Imports System.Data.Common

Namespace BusinessLayer
    Public Class cAddModifyInvestor

#Region "Declarations"
        Private minvesterID As Integer
        Private minvEntityId As String = ""
        Private minvestorName As String = ""
        Private meStatusID As Integer
        Private mwebsite As String = ""
        Private mheadInvestorID As Integer
        Private minvestorTypeID As Integer
        Private mstyleID As Integer
        Private mturnoverID As Integer
        Private mcountryId As Integer
        Private mpostalCode As String = ""
        Private mparentID As Integer
        Private multimateParentID As Integer
        Private mmInvestorTypeID As Integer
        Private mactivistID As Integer
        Private mlastReportDate As Date = Nothing
        Private mequityAssets As Int64
        Private mstreet1 As String = ""
        Private mstreet2 As String = ""
        Private mcityID As Integer
        Private mstateID As Integer
        Private mmPhoneNumber As String = ""
        Private mtPhoneNumber As String = ""
        Private mtExtension As String = ""
        Private mfax1 As String = ""
        Private mfax2 As String = ""
        Private memailAddress As String = ""
        Private misCIRInvester As Boolean
        Private msourceTypeID As Integer
        Private mcreatedDate As DateTime
        Private mmodifiedDate As DateTime
        Private mcreatedBy As Integer
        Private mmodifiedBy As Integer


#End Region

#Region "Properties"
        Public Property investorID() As Integer
            Get
                Return minvesterID
            End Get
            Set(ByVal value As Integer)
                minvesterID = value
            End Set
        End Property
        Public Property intEntityId() As Integer
            Get
                Return minvEntityId
            End Get
            Set(ByVal value As Integer)
                minvEntityId = value
            End Set
        End Property
        Public Property investorName() As String
            Get
                Return minvestorName
            End Get
            Set(ByVal value As String)
                minvestorName = value
            End Set
        End Property
        Public Property statusID() As String
            Get
                Return meStatusID
            End Get
            Set(ByVal value As String)
                meStatusID = value
            End Set
        End Property
        Public Property website() As String
            Get
                Return mwebsite
            End Get
            Set(ByVal value As String)
                mwebsite = value
            End Set
        End Property

        Public Property headInvestorID() As Integer
            Get
                Return mheadInvestorID
            End Get
            Set(ByVal value As Integer)
                mheadInvestorID = value
            End Set
        End Property
        Public Property investorTypeid() As String
            Get
                Return minvestorTypeID
            End Get
            Set(ByVal value As String)
                If (value = 0) Then
                    minvestorTypeID = Nothing
                Else
                    minvestorTypeID = value
                End If
            End Set
        End Property

        Public Property turnoverID() As Integer
            Get
                Return mturnoverID
            End Get
            Set(ByVal value As Integer)
                mturnoverID = value
            End Set
        End Property

        Public Property styleID() As Integer
            Get
                Return mstyleID
            End Get
            Set(ByVal value As Integer)
                mstyleID = value
            End Set
        End Property

        Public Property countryID() As Integer
            Get
                Return mcountryId
            End Get
            Set(ByVal value As Integer)
                mcountryId = value
            End Set
        End Property
        Public Property postalCode() As String
            Get
                Return mpostalCode
            End Get
            Set(ByVal value As String)
                mpostalCode = value
            End Set
        End Property
        Public Property parentId() As String
            Get
                Return mparentID
            End Get
            Set(ByVal value As String)
                If (value = 0) Then
                    mparentID = Nothing
                Else
                    mparentID = value
                End If
            End Set
        End Property
        Public Property ultimateparentId() As String
            Get
                Return multimateParentID
            End Get
            Set(ByVal value As String)
                If (value = 0) Then
                    multimateParentID = Nothing
                Else
                    multimateParentID = value
                End If
            End Set
        End Property
        Public Property masterInvestorTypeid() As String
            Get
                Return mmInvestorTypeID
            End Get
            Set(ByVal value As String)
                If (value = 0) Then
                    mmInvestorTypeID = Nothing
                Else
                    mmInvestorTypeID = value
                End If
            End Set
        End Property
        Public Property activistID() As Integer
            Get
                Return mactivistID
            End Get
            Set(ByVal value As Integer)
                mactivistID = value
            End Set
        End Property
        Public Property lastReportDate() As Date
            Get
                Return mlastReportDate
            End Get
            Set(ByVal value As Date)
                'If value = "#12:00:00 AM#" Then
                '    mlastReportDate = Nothing
                'Else
                mlastReportDate = value
                'End If
            End Set
        End Property
        Public Property equityAssets() As Int64
            Get
                Return mequityAssets
            End Get
            Set(ByVal value As Int64)
                mequityAssets = value
            End Set
        End Property
        Public Property street1() As String
            Get
                Return mstreet1
            End Get
            Set(ByVal value As String)
                mstreet1 = value
            End Set
        End Property
        Public Property street2() As String
            Get
                Return mstreet2
            End Get
            Set(ByVal value As String)
                mstreet2 = value
            End Set
        End Property
        Public Property cityID() As Integer
            Get
                Return mcityID
            End Get
            Set(ByVal value As Integer)
                mcityID = value
            End Set
        End Property
        Public Property stateID() As Integer
            Get
                Return mstateID
            End Get
            Set(ByVal value As Integer)
                mstateID = value
            End Set
        End Property
        Public Property mPhoneNumber() As String
            Get
                Return mmPhoneNumber
            End Get
            Set(ByVal value As String)
                mmPhoneNumber = value
            End Set
        End Property
        Public Property tPhoneNumber() As String
            Get
                Return mtPhoneNumber
            End Get
            Set(ByVal value As String)
                mtPhoneNumber = value
            End Set
        End Property
        Public Property tExtention() As String
            Get
                Return mtExtension
            End Get
            Set(ByVal value As String)
                mtExtension = value
            End Set
        End Property

        Public Property faxPermanent() As String
            Get
                Return mfax1
            End Get
            Set(ByVal value As String)
                mfax1 = value
            End Set
        End Property

        Public Property fax2() As String
            Get
                Return mfax2
            End Get
            Set(ByVal value As String)
                mfax2 = value
            End Set
        End Property

        Public Property emailAddress() As String
            Get
                Return memailAddress
            End Get
            Set(ByVal value As String)
                memailAddress = value
            End Set
        End Property

        Public Property isCIRInvestor() As Boolean
            Get
                Return misCIRInvester
            End Get
            Set(ByVal value As Boolean)
                misCIRInvester = value
            End Set
        End Property

        Public Property sourceTypeID() As Integer
            Get
                Return msourceTypeID
            End Get
            Set(ByVal value As Integer)
                msourceTypeID = value
            End Set
        End Property

        Public Property createdDate() As DateTime
            Get
                Return mcreatedDate
            End Get
            Set(ByVal value As DateTime)
                mcreatedDate = value
            End Set
        End Property
        Public Property modifyDate() As DateTime
            Get
                Return mmodifiedDate
            End Get
            Set(ByVal value As DateTime)
                mmodifiedDate = value
            End Set
        End Property
        Public Property createdBy() As Integer
            Get
                Return mcreatedBy
            End Get
            Set(ByVal value As Integer)
                mcreatedBy = value
            End Set
        End Property
        Public Property modifyBy() As Integer
            Get
                Return mmodifiedBy
            End Get
            Set(ByVal value As Integer)
                mmodifiedBy = value
            End Set
        End Property

        'Public Property RegionId() As String
        '    Get
        '        Return m_regionId
        '    End Get
        '    Set(ByVal value As String)
        '        If (value = 0) Then
        '            m_regionId = Nothing
        '        Else
        '            m_regionId = value
        '        End If
        '    End Set
        'End Property
#End Region

#Region "Acquisition/Merger/Changed Name to"

        Public Shared Function CheckInvestorsExistence(ByVal InvIds As String) As DataSet
            Dim sp As String = "Cp_Sp_Inv_ValidateInv"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvIds})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

#Region "Finding parent of acquired investor"

        Public Shared Function CheckParentIdForInvestor(ByVal InvestorId As Integer) As String
            Dim baseclass As BaseClassDataContext = Nothing
            '            Dim objInv As CP_Investor = Nothing
            Try
                baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
                Dim query = (From p In baseclass.GetTable(Of CP_Investor)() Where p.InvestorID = _
                             InvestorId Select p.ParentID).SingleOrDefault
                If (query Is Nothing) Then
                    Return "0"
                Else
                    Return query.ToString
                End If
            Catch ex As Exception
                Throw ex
                Return "0"
            End Try
        End Function

#End Region

#Region "Log table for an investor"

        ''' <summary>
        ''' Return the Investor log
        ''' </summary>
        ''' <param name="InvestorId"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function LogTable(ByVal InvestorId As Integer) As IDataReader
            Dim StoredProcedure As String = "CR_Sp_Inv_ShowPresentInvestorLog"
            Dim idr As IDataReader = Nothing
            Try
                idr = CreateDataBase().ExecuteReader(StoredProcedure, New Object() {InvestorId})
                Return idr
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

#End Region

#Region "Save Log"

        ''' <summary>
        ''' For Changed Name
        ''' </summary>
        ''' <param name="previousInvid"></param>
        ''' <param name="previousInvName"></param>
        ''' <param name="CurrentInvId"></param>
        ''' <param name="CurrentInvName"></param>
        ''' <param name="previousUltimateParent"></param>
        ''' <param name="processType"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        ''' 
        Public Shared Function CreateLog(ByVal previousInvid As Integer, ByVal previousInvName As String, _
                                ByVal CurrentInvId As Integer, ByVal CurrentInvName As String, _
                                ByVal previousParent As Integer, ByVal processType As String) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            Dim logTable As CP_InvestorAcruirerStatus = Nothing
            Try
                baseclass = New BaseClassDataContext(System.Configuration.ConfigurationManager.AppSettings("smtpserver").ToString())
                logTable = New CP_InvestorAcruirerStatus
                logTable.PreviousInvestorId = previousInvid
                logTable.ADate = Date.Today.Date
                logTable.PreviousInvestorName = previousInvName
                If (previousParent <> 0) Then
                    logTable.PreviousUltimateParentId = previousParent
                End If
                logTable.changeNameOrAcquiredOrMerged = processType
                logTable.FlagImportOrManual = 1
                logTable.CurrentInvestorId = CurrentInvId
                logTable.CurrentInvestorName = CurrentInvName
                baseclass.CP_InvestorAcruirerStatus.InsertOnSubmit(logTable)
                baseclass.SubmitChanges()
                Return True
            Catch ex As Exception
                Throw ex
                Return False
            Finally
                baseclass = Nothing
                logTable = Nothing
            End Try
        End Function

        ''' <summary>
        ''' For acquirer Investor
        ''' </summary>
        ''' <param name="previousInvid"></param>
        ''' <param name="previousInvName"></param>
        ''' <param name="CurrentInvId"></param>
        ''' <param name="CurrentInvName"></param>
        ''' <param name="previousUltimateParent"></param>
        ''' <param name="processType"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function CreateLogForAcquirer(ByVal previousInvid As Integer, ByVal previousInvName As String, _
                                  ByVal CurrentInvId As Integer, ByVal CurrentInvName As String, _
                                  ByVal previousParent As Integer, ByVal processType As String) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            Dim logTable As CP_InvestorAcruirerStatus = Nothing
            Dim Investor As CP_Investor = Nothing
            'Dim UltimateParent_New As Integer = 0
            Dim Parent As CP_Parent = Nothing
            Dim mapOld As List(Of cp_InvestorFundMapping) = Nothing
            Dim Mapnew As List(Of cp_InvestorFundMapping) = Nothing
            Dim map As List(Of cp_InvestorFundMapping) = Nothing
            Dim mapChild As cp_InvestorFundMapping = Nothing
            Try
                map = New List(Of cp_InvestorFundMapping)
                baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
                logTable = New CP_InvestorAcruirerStatus
                'Creating the log in CP_InvestorAcruirerStatus table
                logTable.PreviousInvestorId = previousInvid
                logTable.ADate = Date.Today.Date
                logTable.PreviousInvestorName = previousInvName
                'Checking whethre the ultimate parent of the acquired investor exist.
                'otherwise send the null value
                If (previousParent <> 0) Then
                    logTable.PreviousUltimateParentId = previousParent
                Else
                    logTable.PreviousUltimateParentId = Nothing
                End If
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                logTable.changeNameOrAcquiredOrMerged = processType
                logTable.FlagImportOrManual = 1
                logTable.CurrentInvestorId = CurrentInvId
                logTable.CurrentInvestorName = CurrentInvName
                baseclass.CP_InvestorAcruirerStatus.InsertOnSubmit(logTable)
                baseclass.SubmitChanges()
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                'Updaing the investor table for nle and ultimate parent values for acquired investor.
                Investor = (From p In baseclass.GetTable(Of CP_Investor)() Where p.InvestorID = previousInvid Select p).SingleOrDefault
                If (Not Investor Is Nothing) Then
                    'Nle the investor and change it's ultimate parent
                    Investor.NLE = True
                    Parent = (From p In baseclass.GetTable(Of CP_Parent)() Where p.Parent = CurrentInvName Select p).SingleOrDefault
                    If (Parent Is Nothing) Then
                        'creating a utimate parent of acquired investor.
                        'because the acquirer does not exist in the ultimate parent table.
                        Parent = New CP_Parent
                        Parent.Parent = CurrentInvName
                        Parent.isdefault = 0
                        baseclass.CP_Parents.InsertOnSubmit(Parent)
                        baseclass.SubmitChanges()
                        Investor.ParentID = Parent.ParentID
                    Else
                        'Updaeing the ultimate parent of acquired investor
                        Investor.ParentID = Parent.ParentID
                    End If
                End If
                baseclass.SubmitChanges()
                ''''''''''''''''''''''''''''''''''''''''
                'Copying the investments to the acquirer................................................
                ' check maping for acquired investor
                mapOld = (From p In baseclass.GetTable(Of cp_InvestorFundMapping)() Where p.InvestorID = previousInvid _
                      Select p).ToList
                'check maping for acquirer investor
                Mapnew = (From p In baseclass.GetTable(Of cp_InvestorFundMapping)() Where p.InvestorID = CurrentInvId _
                      Select p).ToList
                If (mapOld.Count > 0) Then
                    If (Mapnew.Count = 0) Then
                        'case when acquirer do not hav any fund
                        For i As Integer = 0 To mapOld.Count - 1
                            mapChild = New cp_InvestorFundMapping
                            mapChild.InvestorID = CurrentInvId
                            mapChild.FundID = mapOld.ElementAt(i).FundID
                            map.Add(mapChild)
                        Next
                        If (map.Count > 0) Then
                            baseclass.cp_InvestorFundMappings.InsertAllOnSubmit(map)
                            baseclass.SubmitChanges()
                        End If
                    Else
                        '                        Dim newm As Integer = 0
                        Dim old As Integer = 0
                        'case when acquirer has funds and need to be compared with the acruired
                        For i As Integer = 0 To mapOld.Count - 1
                            old = mapOld.ElementAt(i).FundID
                            Mapnew = (From p In baseclass.GetTable(Of cp_InvestorFundMapping)() Where _
                                      p.InvestorID = CurrentInvId And p.FundID = old Select p).ToList
                            If (Mapnew.Count = 0) Then
                                mapChild = New cp_InvestorFundMapping
                                mapChild.InvestorID = CurrentInvId
                                mapChild.FundID = old
                                map.Add(mapChild)
                            End If
                        Next
                        If (map.Count > 0) Then
                            baseclass.cp_InvestorFundMappings.InsertAllOnSubmit(map)
                            baseclass.SubmitChanges()
                        End If
                    End If
                End If

                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                Return True
            Catch ex As Exception
                Throw ex
                Return False
            Finally
                baseclass = Nothing
                logTable = Nothing
                Investor = Nothing
                'UltimateParent_New = Nothing
                Parent = Nothing
            End Try
        End Function

        Public Function CreateLogForMerger(ByVal previousInvid As Integer, ByVal previousInvName As String, _
                             ByVal CurrentInv() As DataRow, ByVal previousParent As Integer, _
                             ByVal processType As String) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            Dim logTable As CP_InvestorAcruirerStatus = Nothing
            '            Dim Investor As CP_Investor = Nothing
            Dim Parent_New As Integer = 0
            '            Dim investorId As Integer = 0
            '            Dim UltimateParent As CP_UltimateParent = Nothing
            '            Dim mapOld As List(Of cp_InvestorFundMapping) = Nothing
            '            Dim Mapnew As List(Of cp_InvestorFundMapping) = Nothing
            Dim map As List(Of cp_InvestorFundMapping) = Nothing
            Dim mapChild As cp_InvestorFundMapping = Nothing
            Dim oldinv As List(Of cp_InvestorFundMapping) = Nothing
            Dim MapLog As List(Of CP_InvestorAcruirerStatus) = Nothing
            Dim oldFund As Integer = 0
            Dim InvId As Integer = 0
            Try
                'Dim asq = From p In CurrentInv.AsQueryable() _
                '   Select New With _
                '     { _
                '       .InvestorName = p("InvestorName"), _
                '       .InvestorID = p("investorid") _
                '     }
                '  Dim query = (From p In CurrentInv Select New p())
                map = New List(Of cp_InvestorFundMapping)
                MapLog = New List(Of CP_InvestorAcruirerStatus)
                baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
                For invIds As Integer = 0 To CurrentInv.Count - 1
                    'Create log
                    logTable = New CP_InvestorAcruirerStatus
                    logTable.PreviousInvestorId = CurrentInv(invIds).Item("InvestorId").ToString
                    logTable.ADate = Date.Today.Date
                    logTable.PreviousInvestorName = CurrentInv(invIds).Item("Investor Name").ToString
                    Parent_New = CheckParentIdForInvestor(logTable.PreviousInvestorId)
                    'Checking whether the ultimate parent of the acquired investor exist.
                    'otherwise send the null value
                    If (Parent_New = 0) Then
                        logTable.PreviousUltimateParentId = Nothing
                    Else
                        logTable.PreviousUltimateParentId = Parent_New
                    End If
                    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                    logTable.changeNameOrAcquiredOrMerged = processType
                    logTable.FlagImportOrManual = 1
                    logTable.CurrentInvestorId = previousInvid
                    logTable.CurrentInvestorName = previousInvName
                    MapLog.Add(logTable)
                    ''''''''''''''''''''''''
                    'Copying the investments'''''''''''''''''''''
                    InvId = CurrentInv(invIds).Item("InvestorId").ToString
                    oldinv = (From p In baseclass.GetTable(Of cp_InvestorFundMapping)() Where _
                                       p.InvestorID = InvId Select p Order By p.FundID).ToList
                    For old As Integer = 0 To oldinv.Count - 1
                        oldFund = oldinv.ElementAt(old).FundID
                        Dim exist = (From p In map Where p.FundID = oldFund Select p.FundID).SingleOrDefault
                        If (Not exist = 0) Then
                            Continue For
                        End If
                        mapChild = (From p In baseclass.GetTable(Of cp_InvestorFundMapping)() Where _
                        p.InvestorID = previousInvid And p.FundID = oldFund Select p Order By p.FundID).SingleOrDefault
                        If (mapChild Is Nothing) Then
                            mapChild = New cp_InvestorFundMapping
                            mapChild.InvestorID = previousInvid
                            mapChild.FundID = oldFund
                            map.Add(mapChild)
                        End If
                    Next
                    ''''''''''''''''''''''''''''''''''''''''''''
                Next
                baseclass.CP_InvestorAcruirerStatus.InsertAllOnSubmit(MapLog)
                baseclass.cp_InvestorFundMappings.InsertAllOnSubmit(map)
                baseclass.SubmitChanges()
                Return True
            Catch ex As Exception
                Throw ex
                Return False
            Finally
                baseclass = Nothing
                logTable = Nothing
                'Investor = Nothing
                Parent_New = Nothing
                'investorId = Nothing
                logTable = Nothing
                map = Nothing
                mapChild = Nothing
            End Try
        End Function

#End Region

#Region "Checking whether an investor is marked NLe or not"

        Public Shared Function CheckNleOrNot(ByVal InvestorId As Integer) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            'Dim Investor As Boolean = Nothing
            Try
                baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
                Dim query = (From p In baseclass.GetTable(Of CP_Investor)() _
                Where p.InvestorID = InvestorId Select nl = p.NLE).SingleOrDefault
                If (query Is Nothing) Then Return False
                Return query.Value
            Catch ex As Exception
                Throw ex
                Return True
            End Try
        End Function

#End Region

#End Region

#Region "Enum"
        Public Enum GridSelect
            NewInvestor
            SuggestedInvestors
        End Enum
#End Region

#Region "Functions"

        Public Shared Function GetCountryName(ByVal CountryId As Integer) As String
            Dim objexecres As New ExecutionResult
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@CountryId", CountryId)
            objexecres = CoreFunctions.Execute(strConfiguration, "CR_sp_GetCountryDetail", CommandType.StoredProcedure, ReturnTypes.Scalar, param)
            Return CType(objexecres.ResultObject, String)
        End Function

        Public Function IsInvestorLocked(ByVal investorid As Integer) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            Dim exist As Boolean = False
            ' Try
            baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
            exist = (From p In baseclass.GetTable(Of CP_Investor)() _
                  Where p.InvestorID = investorid Select p.IsLocked).SingleOrDefault
            Return exist
            'Catch ex As Exception
            '    Throw ex
            '    Return False
            'End Try
        End Function
        ''' <summary>
        ''' This shall check whether the master inverter type of investor is active or not.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function CheckMasterInvestorTypeIsActive(ByVal MasterInvestorTypeId As Integer) As Boolean
            Dim baseclass As BaseClassDataContext = Nothing
            Dim exist As Boolean = False
            ' Try
            baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
            baseclass.CommandTimeout = 0
            exist = (From p In baseclass.GetTable(Of CP_MasterInvestorType)() _
                     Where p.MInvestorTypeID = MasterInvestorTypeId Select p.Active).SingleOrDefault
            Return exist
            'Catch ex As Exception
            '    Throw ex
            '    Return False
            'Finally
            '    baseclass = Nothing
            'End Try
        End Function

        Public Shared Function GetFundsByInvID(ByVal InvIds As Integer) As DataSet
            Dim sp As String = "CR_sp_INV_GetFunds"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvIds})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Shared Function GetInvestorNameCount(ByVal InvestorName As String) As DataSet
            Dim sp As String = "CR_sp_INV_NameCount"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvestorName})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

        Public Function getTotalInvestorrecords()
            Dim baseclass As BaseClassDataContext = Nothing
            Dim total As Integer = 0
            'Try
            baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
            total = (From p In baseclass.GetTable(Of CP_Investor)() Select p.InvestorID).Count
            Return total
            'Catch ex As Exception
            '    Throw ex
            '    Throw ex
            'End Try
        End Function

        ''' <summary>
        ''' Fetching the data from master table
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function GetOverViewDataSet() As IDataReader
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_GetDetail", CommandType.StoredProcedure, ReturnTypes.DataReader)
            Return CType(obj.ResultObject, IDataReader)
        End Function

        Public Shared Function GetAdditionalDetail() As IDataReader
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_AdditionalDetail", CommandType.StoredProcedure, ReturnTypes.DataReader)
            Return CType(obj.ResultObject, IDataReader)
        End Function


        Public Function GetRegionForCountry(ByVal countryid As Integer) As DataSet
            Dim dtReturn As DataSet = Nothing
            dtReturn = CreateDataBase().ExecuteDataSet("CR_sp_GetRegionDetail", _
            New Object() {countryid})
            Return dtReturn
        End Function

        Public Function Insert_Update_Overview(ByVal InvestorID As Integer, ByVal Invester_Name As String, ByVal estatusID As Integer, ByVal website As String, _
                                                ByVal HeadInvestorID As Integer, ByVal Sub_Investor_Type_Id As Integer,
                                                ByVal Style_Id As Integer, ByVal Turnover_id As Integer, _
         ByVal Country_id As Integer, ByVal PostalCode As String, ByVal ParentID As Integer, _
        ByVal UltimateParentID As Integer, ByVal MInvestorTypeID As Integer, ByVal ActivistID As Integer, _
            ByVal LastReportDate As Date, ByVal EquityAssets As Int64, ByVal Street1 As String, ByVal Street2 As String, _
        ByVal CityID As Integer, ByVal StateID As Integer, ByVal MPhoneNumber As String, ByVal TPhoneNumber As String _
        , ByVal TExtension As String, ByVal FaxNumber1 As String, ByVal FaxNumber2 As String, ByVal EMailAddress As String _
        , ByVal IsCIRInvestor As Boolean, ByVal SourceTypeID As Integer, ByVal CreatedDate As DateTime, ByVal ModifiedDate As DateTime, _
        ByVal CreatedBy As Integer, ByVal ModifiedBy As Integer, ByVal pintProjectID As Integer, ByVal pstrInvEntityId As String, ByVal lblnEditInDataHub As Boolean) As DataSet
            Dim ds As DataSet = Nothing
            Dim obj As ExecutionResult = Nothing
            Dim Logmessage As New System.Text.StringBuilder
            Dim val As Integer = 0
            While (True)
                If Not lblnEditInDataHub Then
                    Dim param(32) As SqlParameter
                    param(0) = New SqlParameter("@InvestorID", InvestorID)
                    param(1) = New SqlParameter("@Investor_Name", Invester_Name)
                    param(2) = New SqlParameter("@EStatusID", estatusID)
                    param(3) = New SqlParameter("@Website", website)
                    param(4) = New SqlParameter("@HeadInvestorID", HeadInvestorID)
                    param(5) = New SqlParameter("@Sub_Investor_Type_Id", Sub_Investor_Type_Id)
                    param(6) = New SqlParameter("@Style_Id", Style_Id)
                    param(7) = New SqlParameter("@Turnover_id", Turnover_id)
                    param(8) = New SqlParameter("@Country_id", Country_id)
                    param(9) = New SqlParameter("@PostalCode", PostalCode)
                    param(10) = New SqlParameter("@ParentID", ParentID)
                    param(11) = New SqlParameter("@UltimateParentID", UltimateParentID)
                    param(12) = New SqlParameter("@Master_Investor_Type_Id ", MInvestorTypeID)
                    param(13) = New SqlParameter("@ActivistID", ActivistID)
                    If LastReportDate = "#12:00:00 AM#" Then
                        param(14) = New SqlParameter("@LastReportDate", SqlDateTime.Null)
                    Else
                        param(14) = New SqlParameter("@LastReportDate", LastReportDate)
                    End If
                    param(15) = New SqlParameter("@EquityAssets", EquityAssets)
                    param(16) = New SqlParameter("@Street1", Street1)
                    param(17) = New SqlParameter("@Street2", Street2)
                    param(18) = New SqlParameter("@CityID", CityID)
                    param(19) = New SqlParameter("@StateID", StateID)
                    param(20) = New SqlParameter("@MPhoneNumber", MPhoneNumber)
                    param(21) = New SqlParameter("@TPhoneNumber", TPhoneNumber)
                    param(22) = New SqlParameter("@TExtension", TExtension)
                    param(23) = New SqlParameter("@FaxNumber1", FaxNumber1)
                    param(24) = New SqlParameter("@FaxNumber2", FaxNumber2)
                    param(25) = New SqlParameter("@EmailAddress", EMailAddress)
                    param(26) = New SqlParameter("@IsCIRInvestor", IsCIRInvestor)
                    param(27) = New SqlParameter("@SourceTypeID", SourceTypeID)
                    param(28) = New SqlParameter("@CreatedDate", CreatedDate)
                    param(29) = New SqlParameter("@ModifyDate", ModifiedDate)
                    param(30) = New SqlParameter("@CreatedBy", CreatedBy)
                    param(31) = New SqlParameter("@ModifiedBy", ModifiedBy)
                    param(32) = New SqlParameter("@ReturnVal", val)
                    'param(31) = New SqlParameter("@ChangeInvNameSuperholder", lblnChangeInvNameSuperholder)
                    obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_AddInvestor", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
                    ds = CType(obj.ResultObject, DataSet)
                Else
                    Dim param(34) As SqlParameter
                    param(0) = New SqlParameter("@InvestorID", InvestorID)
                    param(1) = New SqlParameter("@Investor_Name", Invester_Name)
                    param(2) = New SqlParameter("@EStatusID", estatusID)
                    param(3) = New SqlParameter("@Website", website)
                    param(4) = New SqlParameter("@HeadInvestorID", HeadInvestorID)
                    param(5) = New SqlParameter("@Sub_Investor_Type_Id", Sub_Investor_Type_Id)
                    param(6) = New SqlParameter("@Style_Id", Style_Id)
                    param(7) = New SqlParameter("@Turnover_id", Turnover_id)
                    param(8) = New SqlParameter("@Country_id", Country_id)
                    param(9) = New SqlParameter("@PostalCode", PostalCode)
                    param(10) = New SqlParameter("@ParentID", ParentID)
                    param(11) = New SqlParameter("@UltimateParentID", UltimateParentID)
                    param(12) = New SqlParameter("@Master_Investor_Type_Id ", MInvestorTypeID)
                    param(13) = New SqlParameter("@ActivistID", ActivistID)
                    If LastReportDate = "#12:00:00 AM#" Then
                        param(14) = New SqlParameter("@LastReportDate", SqlDateTime.Null)
                    Else
                        param(14) = New SqlParameter("@LastReportDate", LastReportDate)
                    End If
                    param(15) = New SqlParameter("@EquityAssets", EquityAssets)
                    param(16) = New SqlParameter("@Street1", Street1)
                    param(17) = New SqlParameter("@Street2", Street2)
                    param(18) = New SqlParameter("@CityID", CityID)
                    param(19) = New SqlParameter("@StateID", StateID)
                    param(20) = New SqlParameter("@MPhoneNumber", MPhoneNumber)
                    param(21) = New SqlParameter("@TPhoneNumber", TPhoneNumber)
                    param(22) = New SqlParameter("@TExtension", TExtension)
                    param(23) = New SqlParameter("@FaxNumber1", FaxNumber1)
                    param(24) = New SqlParameter("@FaxNumber2", FaxNumber2)
                    param(25) = New SqlParameter("@EmailAddress", EMailAddress)
                    param(26) = New SqlParameter("@IsCIRInvestor", IsCIRInvestor)
                    param(27) = New SqlParameter("@SourceTypeID", SourceTypeID)
                    param(28) = New SqlParameter("@CreatedDate", CreatedDate)
                    param(29) = New SqlParameter("@ModifyDate", ModifiedDate)
                    param(30) = New SqlParameter("@CreatedBy", CreatedBy)
                    param(31) = New SqlParameter("@ModifiedBy", ModifiedBy)
                    param(32) = New SqlParameter("@ReturnVal", val)
                    param(33) = New SqlParameter("@ProjectID", pintProjectID)
                    param(34) = New SqlParameter("@InvEntityID", pstrInvEntityId)
                    obj = CoreFunctions.Execute(strConfiguration, "CR_sp_DE_InsertInvestorLog", CommandType.StoredProcedure, ReturnTypes.DataSet, param)
                    ds = CType(obj.ResultObject, DataSet)
                End If

                If (ds IsNot Nothing) Then
                    For i As Integer = 0 To ds.Tables.Count - 1
                        If (ds.Tables(i).Columns.Contains("errorNumber")) Then
                            Logmessage.Append("An Investor with the following details ")
                            Logmessage.Append(Environment.NewLine)
                            'Logging
                            If (ds.Tables(i).Columns.Contains("errormessage")) Then
                                Logmessage.Append("InvestorName - ")
                                Logmessage.Append(Invester_Name)
                                Logmessage.Append(", Entity_Id - ")
                                Logmessage.Append(ds.Tables(i).Rows(0).Item("Entityid").ToString)
                                Logmessage.Append(", InvestorId - ")
                                Logmessage.Append(InvestorID.ToString)
                                If (InvestorID = 0) Then
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("could not be added in database.")
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("Error Message - ")
                                    Logmessage.Append(ds.Tables(i).Rows(0).Item("errormessage").ToString)
                                    Logmessage.Append("Error Number - ")
                                    Logmessage.Append(ds.Tables(i).Rows(0).Item("errorNumber").ToString)
                                Else
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("could not be updated in database.")
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("Error Message - ")
                                    Logmessage.Append(ds.Tables(i).Rows(0).Item("errormessage").ToString)
                                    Logmessage.Append("Error Number - ")
                                    Logmessage.Append(ds.Tables(i).Rows(0).Item("errorNumber").ToString)
                                End If
                            Else
                                Logmessage.Append("InvestorName - ")
                                Logmessage.Append(Invester_Name)
                                Logmessage.Append(", Entity_Id - ")
                                Logmessage.Append(ds.Tables(i).Rows(0).Item("Entityid").ToString)
                                Logmessage.Append(", InvestorId - ")
                                Logmessage.Append(ds.Tables(i).Rows(0).Item("InvestorId").ToString)
                                If (InvestorID = 0) Then
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("has been added successfully in database. ")
                                Else
                                    Logmessage.Append(Environment.NewLine)
                                    Logmessage.Append("has been updated successfully in database.")
                                End If
                            End If
                            CreateErrorLog(Logmessage)
                            '********************************************************

                            If (ds.Tables(i).Rows(0).Item("errorNumber").ToString <> "") Then
                                If (ds.Tables(i).Rows(0).Item("errorNumber").ToString = "1205") Then
                                    Continue While
                                ElseIf ds.Tables(i).Rows(0).Item("errorNumber").ToString = "3930" Then
                                    Continue While
                                End If
                            End If
                        End If
                    Next
                Else
                    Return Nothing
                End If
                Return ds
            End While
            Logmessage = Nothing

        End Function

        ''' <summary>
        ''' Select the sub investor records
        ''' </summary>
        ''' <param name="strCountryCode"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function ShowSubFirms(ByVal strCountryCode As Integer) As IDataReader
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@MasterInvestorId", strCountryCode)
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_GetInvTypeDetail", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(obj.ResultObject, IDataReader)
        End Function

        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="strInvID"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetMasterByInvID(ByVal strInvID As Integer) As DataSet
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@InvestorTypeId", strInvID)
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_GetMasterType", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(obj.ResultObject, DataSet)
        End Function

        Public Function MasterInvestorTypeByInvestorTypeId(ByVal InvestorTypeId As Integer) As IDataReader
            Dim dtReturn As IDataReader = Nothing
            dtReturn = CreateDataBase().ExecuteReader("CR_sp_INV_GetMasterInvestor", New Object() {InvestorTypeId})
            Return dtReturn
        End Function

        ''' <summary>
        ''' Show overview data
        ''' </summary>
        ''' <param name="InvestorId"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function ShowOverviewData(ByVal InvestorId As Integer) As IDataReader
            Dim dtReturn As IDataReader = Nothing
            dtReturn = CreateDataBase().ExecuteReader("CR_sp_INV_GetInvestorDetail", New Object() {InvestorId})
            Return dtReturn

        End Function


        Public Function ShowIDByEnv(ByVal InvestorId As String) As IDataReader
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@EnvEntityId", InvestorId)
            Dim obj As ExecutionResult = Nothing
            obj = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_GetInvestorID", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(obj.ResultObject, IDataReader)
        End Function

        Public Shared Function Delete_Investor(ByVal InvestorId As String) As String

            'Const SEL_TestProcedure = "CP_Sp_INV_DEL_Investors"
            Dim dtReturn As String
            Dim sqlparam(0) As SqlParameter
            Dim cmd As New SqlCommand
            Dim Logmessage As New System.Text.StringBuilder
            'Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "CR_sp_INV_Delete"
            cmd.CommandTimeout = 0
            sqlparam(0) = New SqlParameter("@investor_Id", InvestorId)
            cmd.Parameters.Add(sqlparam(0))
            'dtReturn = CreateDataBase().ExecuteScalar("CP_Sp_INV_DEL_Investors", New Object() {InvestorId})
            dtReturn = CreateDataBase().ExecuteScalar(cmd)

            If (dtReturn <> "Investor deleted successfully") Then
                '''''Logging
                Logmessage.Append("InvestorID - ")
                Logmessage.Append(InvestorId)
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Investor can not be deleted successfully.")
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Error Message - ")
                Logmessage.Append(dtReturn)
                CreateErrorLog(Logmessage)
                ''''''''''''''''''''''''''''
            Else
                '''''Logging
                Logmessage.Append("InvestorID - ")
                Logmessage.Append(InvestorId)
                Logmessage.Append(Environment.NewLine)
                Logmessage.Append("Investor deleted successfully.")
                'CreateErrorLog(Logmessage)
                ''''''''''''''''''''''''''''
            End If
            Logmessage = Nothing
            Return dtReturn

            'Catch ex As Exception
            '    Throw New Exception("Data can not be inserted.")
            'End Try
        End Function


        Public Shared Function UpdateInvestorStatusToDelete(ByVal InvestorId As String) As String

            'Const SEL_TestProcedure = "CP_Sp_INV_DEL_Investors"
            Dim dtReturn As String
            Dim sqlparam(0) As SqlParameter
            Dim cmd As New SqlCommand
            Dim Logmessage As New System.Text.StringBuilder
            'Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "CR_sp_INV_UpdateStatus"
            cmd.CommandTimeout = 0
            sqlparam(0) = New SqlParameter("@investor_Id", InvestorId)
            cmd.Parameters.Add(sqlparam(0))
            'dtReturn = CreateDataBase().ExecuteScalar("CP_Sp_INV_DEL_Investors", New Object() {InvestorId})
            dtReturn = CreateDataBase().ExecuteScalar(cmd)
            Logmessage = Nothing
            Return dtReturn

            'Catch ex As Exception
            '    Throw New Exception("Data can not be inserted.")
            'End Try
        End Function


        Public Sub UpdateInvestorNLE(ByVal alertId As Integer, ByVal entityId As Integer, ByVal primaryKeyId As Integer)
            Dim objClass As New BaseClassDataContext(BLLmdlCommon.strConfiguration)
            'Try
            Dim updatedval = (From a In objClass.cp_ALT_Alerts _
                                       Where a.AlertID = alertId And a.EntityID = entityId And a.PrimaryKeyID = primaryKeyId _
                                       Select a.UpdateColumnValue).SingleOrDefault
            Dim UpdateNLE = (From p In objClass.GetTable(Of CP_Investor)() _
                                   Where p.InvestorID = primaryKeyId _
                                   Select p).SingleOrDefault
            With UpdateNLE
                .NLE = updatedval
                objClass.SubmitChanges()
            End With
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Sub
        Public Shared Function CanInvestorDelete(ByVal investorId As Integer) As String
            'Const SEL_TestProcedure = "Cp_sp_INV_DEL_CanInvestorsDelete"
            Dim dtReturn As String
            Dim sqlparam(0) As SqlParameter

            ' Try
            Dim cmd As New SqlCommand
            cmd.CommandText = "CR_sp_INV_CheckDependency" '""
            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.StoredProcedure

            sqlparam(0) = New SqlParameter("@InvestorId", investorId)
            cmd.Parameters.Add(sqlparam(0))
            dtReturn = CreateDataBase().ExecuteScalar(cmd)
            Return dtReturn
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        Public Shared Function CountSuperholderInOtherInvestors(ByVal pstrOriginalSuperholder As String, ByVal pintInvestorID As Integer) As Integer
            Dim baseclass As BaseClassDataContext = Nothing
            baseclass = New BaseClassDataContext(BLLmdlCommon.strConfiguration)
            Return (From p In baseclass.GetTable(Of CP_Investors)() _
            Where p.Superholder = pstrOriginalSuperholder And p.InvestorID <> pintInvestorID Select p).Count
        End Function

        Public Function GetInvestorsByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_INV_GetInvestorType"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Function GetMasterInvestorsByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_GetMasterInvestor"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

        Public Function GetParentByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_INV_GetParentDetail"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Function GetUltimateParentByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_GetUltimateParentDetail"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try

        End Function
        Public Function GetCountryByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_GetCountry"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try

        End Function
        Public Function GetHeadInvestorByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_INV_GetHeadInvestor"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Function GetStyleByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_GetStyleDetail"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Function GetActivistByID(ByVal Id As String) As DataSet
            Dim sp As String = "CR_sp_GetActivist"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {Id})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Function GetTopCity() As DataSet
            Dim dsReturn As DataSet = Nothing
            dsReturn = CreateDataBase().ExecuteDataSet("CR_sp_GetTopCity")
            Return dsReturn
        End Function
        Public Function GetMasterInvestorsByInvID(ByVal InvestorTypeId As Integer) As DataSet
            Dim dtReturn As DataSet
            Dim sqlparam(0) As SqlParameter
            Dim cmd As New SqlCommand
            cmd.CommandText = "CR_sp_INV_GetMasterInvsType" '""
            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.StoredProcedure
            sqlparam(0) = New SqlParameter("@InvestorTypeId", InvestorTypeId)
            cmd.Parameters.Add(sqlparam(0))
            dtReturn = CreateDataBase().ExecuteDataSet(cmd)
            Return dtReturn
        End Function
        Public Function GetTopState() As DataSet
            Dim dsReturn As DataSet = Nothing
            dsReturn = CreateDataBase().ExecuteDataSet("CR_sp_GetTopState")
            Return dsReturn
        End Function
        Public Shared Function GetMasterInvestorID(ByVal InvIds As Integer) As DataSet
            Dim sp As String = "CR_sp_INV_GetMasterTypeID"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvIds})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Shared Function GetStateByInvID(ByVal InvIds As Integer) As DataSet
            Dim sp As String = "CR_sp_INV_GetState"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvIds})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Shared Function GetStateCodeNCityByCountryID(ByVal countryID As Integer) As DataSet
            Dim sp As String = "CR_sp_GetStateCityDetail"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {countryID})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function
        Public Shared Function GetCityNStateByInvID(ByVal InvestorId As Integer) As DataSet
            Dim sp As String = "CR_sp_INV_GetCityNState"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvestorId})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

        Public Function GetISDCodeForCountry(ByVal countryid As Integer) As DataSet
            Dim dtReturn As DataSet = Nothing
            dtReturn = CreateDataBase().ExecuteDataSet("CR_sp_GetISDCode", _
            New Object() {countryid})
            Return dtReturn
        End Function

        Public Shared Function GetEntityIDByInvestorID(ByVal InvestorId As Integer) As DataSet
            Dim sp As String = "CR_sp_INV_GetInvEntityID"
            Try
                Return CreateDataBase().ExecuteDataSet(sp, New Object() {InvestorId})
            Catch ex As Exception
                Throw ex
                Return Nothing
            End Try
        End Function

        Public Shared Function GetMaxHeadInvestorID() As Integer
            'Const SEL_TestProcedure = "Cp_sp_INV_DEL_CanInvestorsDelete"
            Dim dtReturn As String
            Dim isInteger As Boolean
            Dim Id As Integer
            ' Try
            Dim cmd As New SqlCommand
            cmd.CommandText = "CR_sp_INV_GetMaxHeadInvID" '""
            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.StoredProcedure
            dtReturn = CreateDataBase().ExecuteScalar(cmd)
            isInteger = Int32.TryParse(dtReturn, Id)
            Return Id
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function
        Public Shared Function IsUltimateInvestorExist(ByVal UltimateParent As String) As String
            Dim value As String = Nothing
            Dim count As Integer
            Dim isInteger As Boolean
            value = CreateDataBase().ExecuteScalar("CR_sp_INV_UltimateParentCountByName", _
            New Object() {UltimateParent})
            isInteger = Int32.TryParse(value, count)
            Return value
        End Function
        Public Sub InsertUltimateParent(ByVal UltimateParent As String, ByVal isDefault As Boolean)
            Try
                CreateDataBase().ExecuteNonQuery("CR_sp_InsertUltimateParent", _
            New Object() {UltimateParent, isDefault})
            Catch ex As Exception
                Throw ex
            End Try
        End Sub
        Public Sub UpdateUltimateParent(ByVal UltimateParent As String, ByVal Id As Integer)
            Try
                CreateDataBase().ExecuteNonQuery("CR_sp_INV_UpdateUltimateParentName", _
            New Object() {UltimateParent, Id})
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        Public Shared Function GetMaxUltimateParentID() As Integer
            'Const SEL_TestProcedure = "Cp_sp_INV_DEL_CanInvestorsDelete"
            Dim dtReturn As String
            Dim isInteger As Boolean
            Dim Id As Integer
            ' Try
            Dim cmd As New SqlCommand
            cmd.CommandText = "CR_sp_INV_MaxUltimateParentID" '""
            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.StoredProcedure
            dtReturn = CreateDataBase().ExecuteScalar(cmd)
            isInteger = Int32.TryParse(dtReturn, Id)
            Return Id
            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        Public Shared Function GetUltimateParentDetailByInvestorID(ByVal InvestorID As Integer) As DataSet
            Dim dset As DataSet = Nothing
            Try
                dset = CreateDataBase().ExecuteDataSet("CR_sp_INV_GetUltimateParent", _
            New Object() {InvestorID})
            Catch ex As Exception
                Throw ex
            End Try
            Return dset
        End Function
        Public Shared Function InvNameByInvID(ByVal Id As Integer) As String
            Dim value As String = Nothing
            value = CreateDataBase().ExecuteScalar("CR_sp_INV_GetInvName", _
            New Object() {Id})
            Return value
        End Function
        Public Sub UpdateUltimateParentByName(ByVal UltimateParent As String, ByVal UpdatedUltimateParent As String)
            Try
                CreateDataBase().ExecuteDataSet("CR_sp_INV_ChangeUltimateParent", _
            New Object() {UltimateParent, UpdatedUltimateParent})
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        Public Shared Function GetAllUltimateParent() As DataSet
            Dim dset As DataSet = Nothing
            Try
                dset = CreateDataBase().ExecuteDataSet("CR_sp_GetAllUltimateParent", _
            New Object() {})
            Catch ex As Exception
                Throw ex
            End Try
            Return dset
        End Function


        Public Shared Function GetHeadInvestorIDByName(ByVal PrincipalInvestor As String) As Integer
            'Const SEL_TestProcedure = "Cp_sp_INV_DEL_CanInvestorsDelete"

            Dim value As String = Nothing
            Dim count As Integer
            Dim isInteger As Boolean
            value = CreateDataBase().ExecuteScalar("CR_sp_INV_GetHeadInvDetail", _
            New Object() {PrincipalInvestor})
            isInteger = Int32.TryParse(value, count)
            Return count

            'Catch ex As Exception
            '    Throw ex
            'End Try
        End Function

        Public Shared Function GetFundsByInvestors(ByVal Name As String) As IDataReader
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(0) As SqlClient.SqlParameter
            param(0) = New SqlParameter("@FundName", Name)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_GetFundName", CommandType.StoredProcedure, ReturnTypes.DataReader, param)
            Return CType(eReturnVal.ResultObject, IDataReader)
        End Function

        Public Sub InsertInvestorFundLinking(ByVal InvestorID As Integer, ByVal FundID As String)
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(1) As SqlParameter
            param(0) = New SqlParameter("@InvestorID", InvestorID)
            param(1) = New SqlParameter("@CMBdoc", FundID)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_FND_LinkInvestors", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        End Sub
        Public Shared Function GetAllMasterInvetorType() As DataSet
            Dim dset As DataSet = Nothing
            Try
                dset = CreateDataBase().ExecuteDataSet("CR_sp_INV_GetAllMasterInvType", _
            New Object() {})
            Catch ex As Exception
                Throw ex
            End Try
            Return dset
        End Function
        Public Shared Function GetAllRegion() As DataSet
            Dim dset As DataSet = Nothing
            Try
                dset = CreateDataBase().ExecuteDataSet("CR_sp_INV_GetAllRegion", _
            New Object() {})
            Catch ex As Exception
                Throw ex
            End Try
            Return dset
        End Function

        Public Sub InsertInvestorTypeLinking(ByVal MasterTypeID As Integer, ByVal InvestorType As String)
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(1) As SqlParameter
            param(0) = New SqlParameter("@MasterTypeID", MasterTypeID)
            param(1) = New SqlParameter("@InvestorType", InvestorType)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_LinkInvestorType", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        End Sub

        Public Shared Function IsCountryExist(ByVal CountryName As String) As DataSet
            Dim dset As DataSet = Nothing
            Try
                dset = CreateDataBase().ExecuteDataSet("CR_sp_INV_IsCountryExist", _
            New Object() {CountryName})
            Catch ex As Exception
                Throw ex
            End Try
            Return dset
        End Function

        Public Sub InsertRegionCountryLinking(ByVal RegionID As Integer, ByVal CountryCode As String, ByVal CountryName As String)
            Dim eReturnVal As ExecutionResult = Nothing
            Dim param(2) As SqlParameter
            param(0) = New SqlParameter("@RegionID", RegionID)
            param(1) = New SqlParameter("@CountryName", CountryName)
            param(2) = New SqlParameter("@CountryCode", CountryCode)
            eReturnVal = CoreFunctions.Execute(strConfiguration, "CR_sp_INV_LinkCountryRegion", CommandType.StoredProcedure, ReturnTypes.NonQuery, param)
        End Sub

        Public Shared Function GetAllSuggestedInvestor(ByVal pstrInvestorName As String, ByVal IsInvestor As Boolean, ByVal intLength As Integer) As DataTable
            Dim dset As DataSet = Nothing
            dset = CreateDataBase().ExecuteDataSet("CR_sp_INV_GetSuggestedInvestor", New Object() {pstrInvestorName, IsInvestor, intLength})
            Return dset.Tables(0)
        End Function

        Public Shared Function GetDataHubInvestor(ByVal pintInvestorID As Integer, ByVal pstrUserID As Integer, ByVal pintProjectID As Integer, ByVal pblnIsInvestor As Boolean) As IList(Of AddNewInvestor)
            Dim lstData As New List(Of AddNewInvestor)
            Dim lobjData As AddNewInvestor = Nothing
            Dim lentDB As Database = DataFactory.CreateDataBase()
            Try
                Using cmd As SqlCommand = lentDB.GetStoredProcCommand("CR_sp_INV_DataHubInvestor")
                    lentDB.AddInParameter(cmd, "InvestorID", DbType.Int32, pintInvestorID)
                    lentDB.AddInParameter(cmd, "UserID", DbType.Int32, pstrUserID)
                    lentDB.AddInParameter(cmd, "ProjectID", DbType.Int32, pintProjectID)
                    lentDB.AddInParameter(cmd, "IsInvestorSearch", DbType.Boolean, pblnIsInvestor)
                    cmd.CommandTimeout = 0
                    Using reader As IDataReader = lentDB.ExecuteReader(cmd)
                        Dim lintUserIDIndex As Int16 = reader.GetOrdinal("UserID")
                        Dim lintProjectIDIndex As Int16 = reader.GetOrdinal("ProjectID")
                        Dim lintRowNumberIndex As Int16 = reader.GetOrdinal("RowNumber")
                        Dim lintInvestorIDIndex As Int16 = reader.GetOrdinal("InvestorID")
                        Dim lintILinkIDIndex As Int16 = reader.GetOrdinal("LinkID")
                        Dim lintFundIDIndex As Int16 = reader.GetOrdinal("FundID")
                        Dim lstrInvestorName As Int16 = reader.GetOrdinal("InvestorName")
                        Dim lstrFundName As Int16 = reader.GetOrdinal("FundName")
                        Dim lblnIsInvestor As Int16 = reader.GetOrdinal("IsInvestor")
                        Dim lblnIsManaged As Int16 = reader.GetOrdinal("IsManaged")
                        Dim lblnIsCutRow As Int16 = reader.GetOrdinal("IsCutRow")
                        Dim lstrNotes As Int16 = reader.GetOrdinal("Notes")
                        Dim lstrOrigin As Int16 = reader.GetOrdinal("Origin")
                        Dim lstrID As Int16 = reader.GetOrdinal("ID")
                        Dim lstrInvStatusCode As Int16 = reader.GetOrdinal("InvStatusCode")
                        Dim lstrName As Int16 = reader.GetOrdinal("Name")
                        Dim lintMInvestorTypeID As Int16 = reader.GetOrdinal("MInvestorTypeID")
                        Dim lstrHeadInvestor As Int16 = reader.GetOrdinal("Head Investor")
                        Dim lstrParent As Int16 = reader.GetOrdinal("Parent")
                        Dim lstrActivist As Int16 = reader.GetOrdinal("Activist")
                        Dim lstrType As Int16 = reader.GetOrdinal("Type")
                        Dim lstrStyle As Int16 = reader.GetOrdinal("Style")
                        Dim lstrTurnover As Int16 = reader.GetOrdinal("Turnover")
                        Dim lstrCity As Int16 = reader.GetOrdinal("City")
                        Dim lstrState As Int16 = reader.GetOrdinal("State")
                        Dim lstrCountry As Int16 = reader.GetOrdinal("Country")
                        Dim lstrRegion As Int16 = reader.GetOrdinal("Region")
                        Dim lblnIsMasterDB As Int16 = reader.GetOrdinal("IsMasterDB")
                        Dim lintDenseRank As Int16 = reader.GetOrdinal("DenseRank")
                        Dim lintFundDenseRank As Int16 = reader.GetOrdinal("FundDenseRank")
                        Dim lstrStatus As Int16 = reader.GetOrdinal("Status")
                        While reader.Read()
                            lobjData = New AddNewInvestor() With { _
                             .UserId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintUserIDIndex)), _
                             .ProjectId = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintProjectIDIndex)), _
                             .RowNumber = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintRowNumberIndex)), _
                             .InvestorID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintInvestorIDIndex)), _
                             .LinkID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintILinkIDIndex)), _
                             .FundID = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundIDIndex)), _
                             .InvestorName = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvestorName)), _
                             .FundName = DBParseUtils.GetFieldValueString(reader.GetString(lstrFundName)), _
                             .IsInvestor = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsInvestor)), _
                             .IsManaged = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsManaged)), _
                             .IsCutRow = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsCutRow)), _
                             .Notes = DBParseUtils.GetFieldValueString(reader.GetString(lstrNotes)), _
                             .Origin = DBParseUtils.GetFieldValueString(reader.GetString(lstrOrigin)), _
                             .ID = DBParseUtils.GetFieldValueString(reader.GetString(lstrID)), _
                             .InvStatusCode = DBParseUtils.GetFieldValueString(reader.GetString(lstrInvStatusCode)), _
                             .Name = DBParseUtils.GetFieldValueString(reader.GetString(lstrName)), _
                             .MInvestorTypeID = DBParseUtils.GetFieldValueByte(reader.GetByte(lintMInvestorTypeID)), _
                             .HeadInvestor = DBParseUtils.GetFieldValueString(reader.GetString(lstrHeadInvestor).ToString), _
                             .Parent = DBParseUtils.GetFieldValueString(reader.GetString(lstrParent)), _
                             .Activist = DBParseUtils.GetFieldValueString(reader.GetString(lstrActivist)), _
                             .Type = DBParseUtils.GetFieldValueString(reader.GetString(lstrType)), _
                             .Style = DBParseUtils.GetFieldValueString(reader.GetString(lstrStyle)), _
                             .Turnover = DBParseUtils.GetFieldValueString(reader.GetString(lstrTurnover)), _
                             .City = DBParseUtils.GetFieldValueString(reader.GetString(lstrCity)), _
                             .State = DBParseUtils.GetFieldValueString(reader.GetString(lstrState)), _
                             .Country = DBParseUtils.GetFieldValueString(reader.GetString(lstrCountry)), _
                             .Region = DBParseUtils.GetFieldValueString(reader.GetString(lstrRegion)), _
                             .IsMasterDB = DBParseUtils.GetFieldValueString(reader.GetBoolean(lblnIsMasterDB)), _
                             .DenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintDenseRank)), _
                             .FundDenseRank = DBParseUtils.GetFieldValueInt32(reader.GetInt32(lintFundDenseRank)), _
                             .Status = DBParseUtils.GetFieldValueString(reader.GetString(lstrStatus)) _
                            }
                            lstData.Add(lobjData)
                        End While
                    End Using
                End Using
                Return lstData
            Finally
            End Try
        End Function
#End Region


    End Class
End Namespace
